﻿using CodeGenerator.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace CodeGenerator.DataAccess
{
    public class GenerateDataAccess
    {
        #region SQL
        private string SqlTableList = @"SELECT 
	                                                so.name,
	                                                Convert( VARCHAR(10), ep.[value]) AS [description]
                                                FROM 
	                                                sysobjects so(NOLOCK)
	                                                LEFT JOIN sys.extended_properties ep(NOLOCK) ON ep.major_id=so.id AND ep.minor_id=0
                                                WHERE  
	                                                so.[type]='U' AND so.name<>'sysdiagrams' 
                                                ORDER BY 
	                                                so.name";

            private string SqlFieldList = @"SELECT  
                                                c.name,
                                                t.name AS [type],
                                                c.length AS maxLength,
                                                c.isnullable AS isNullable,
                                                (
                                                    SELECT COUNT(1) FROM sys.identity_columns ic(NOLOCK) WHERE ic.[object_id]=c.id AND ic.column_id=c.colid 
                                                ) AS isIdentity,
                                                (
                                                    SELECT VALUE FROM   sys.extended_properties ep(NOLOCK) WHERE  ep.major_id = c.id AND ep.minor_id=c.colid
                                                ) AS [description],
                                                [IsPk]=CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype='PK' and parent_obj=c.id and name IN (
                                                    SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = c.id AND colid=c.colid))) THEN 1 ELSE 0 END
                                            FROM 
                                                syscolumns c(NOLOCK)
                                                INNER JOIN sys.tables ts(NOLOCK) ON ts.[object_id] = c.id
                                                INNER JOIN sys.types t(NOLOCK) ON t.system_type_id=c.xtype
                                                INNER JOIN systypes st(NOLOCK) ON st.name=t.name AND st.name<>'sysname'
                                                INNER JOIN sysusers su(NOLOCK) ON st.uid=su.uid AND su.name='sys'
                                                --INNER JOIN syscolumns s(NOLOCK) ON c.[object_id]=s.id
                                            WHERE 
                                                ts.name='{0}'
                                            ORDER BY
                                                c.id ASC";
        #endregion

        public List<TableModel> GetTableList()
        {
            List<TableModel> tableList = new List<TableModel>();

            using (BaseContext context = new BaseContext())
            {
                tableList = context.Database.SqlQuery<TableModel>(SqlTableList).ToList();
            }

            return tableList.Where(o => !o.Name.ToLower().Contains("_dic_")).ToList();
        }

        public List<FieldModel> GetFieldList(string tableName)
        {
            List<FieldModel> fieldList = new List<FieldModel>();

            using (BaseContext context = new BaseContext())
            {
                fieldList = context.Database.SqlQuery<FieldModel>(string.Format(SqlFieldList, tableName)).ToList();
            }
            return fieldList;
        }
    }
}